﻿CREATE PROCEDURE [util].[SetLinkedServer](
	@OperatorXml xml
)
AS
BEGIN
	-- parse the operator xml 
	DECLARE @docHandle int;
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @OperatorXml;

	-- loop through each operator
	DECLARE C_LINKEDSERVERS CURSOR FOR(
		SELECT
			[Server]
			,ISNULL([ProductName],N'')
			,[ProviderName]
			,[DataSource]
			,[Location]
			,[ProviderString]
			,[Catalog]
			,ISNULL([OverWrite], 1)
		FROM OPENXML(@docHandle, N'/Root/LinkedServer') 
		WITH (
			[Server] sysname '@Server' --no default. 
			,[ProductName] nvarchar(128) '@ProductName' --default of NULL
			,[ProviderName] nvarchar(128) '@ProviderName' --default of NULL
			,[DataSource] nvarchar(4000) '@DataSource'
			,[Location] nvarchar(4000) '@Location' --default of NULL
			,[ProviderString] nvarchar(4000) '@ProviderString' --default of NULL
			,[Catalog] sysname '@Catalog' --default of NULL
			,[OverWrite] bit '@OverWrite' --default of 1
		)
	)
	OPEN C_LINKEDSERVERS
	DECLARE @Server sysname, @ProductName nvarchar(128), @ProviderName nvarchar(128), @DataSource nvarchar(4000),
		@Location nvarchar(4000), @ProviderString nvarchar(4000), @Catalog sysname, @OverWrite bit;

	FETCH NEXT FROM C_LINKEDSERVERS INTO @Server, @ProductName, @ProviderName, @DataSource, @Location, @ProviderString, @Catalog, @OverWrite;

	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		IF EXISTS(
			SELECT * FROM [master].[sys].[sysservers] 
			WHERE ([srvname] = @Server)
		) AND (@OverWrite = 1)
			BEGIN
				EXEC [msdb].[dbo].[sp_dropserver]
					@server = @Server;
			END

		IF NOT EXISTS(
			SELECT * FROM [master].[sys].[sysservers] 
			WHERE ([srvname] = @Server)
		)
			BEGIN
				EXEC [msdb].[dbo].[sp_addlinkedserver]
					@server = @Server
					,@srvproduct = @ProductName
					,@provider = @ProviderName
					,@datasrc = @DataSource
					,@location = @Location
					,@provstr = @ProviderString
					,@catalog = @Catalog;

			END

		FETCH NEXT FROM C_LINKEDSERVERS INTO @Server, @ProductName, @ProviderName, @DataSource, @Location, @ProviderString, @Catalog, @OverWrite;
	END
	CLOSE C_LINKEDSERVERS
	DEALLOCATE C_LINKEDSERVERS

	-- cleanup xml
	EXEC sp_xml_removedocument @docHandle;
END
